package nl.hu.verbrandendephoenix.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import nl.hu.verbrandendephoenix.shared.Auction;
import nl.hu.verbrandendephoenix.shared.Bid;

public class StatMethods {

	public static ArrayList<Bid> getAllBidsFromID(int auctionID, Connection con) {
		ArrayList<Bid> bids = new ArrayList<Bid>();
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * from Bids WHERE AuctionID = '" + auctionID + "' ORDER BY Biddate ASC" );
			while(rs.next())
			{
				Bid b = new Bid();
				b.setBidID(rs.getInt("BIDID"));
				b.setUserID(rs.getInt("USERID"));
				b.setAuctionID(rs.getInt("AUCTIONID"));
				b.setPrice(rs.getInt("PRICE"));
				b.setDate(rs.getDate("BIDDATE"));
				bids.add(b);
			}
			stmt.close();
			con.close();
		}
		catch(SQLException ex) {
			ex.printStackTrace();
		}
		int size = bids.size();
		
		if (size < 2)
			return null;
		
		return bids;
	}

	public static ArrayList<Auction> getTopAuctions(Connection con) {
		ArrayList<Auction> auctions = new ArrayList<Auction>();
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT AuctionID, COUNT(Bidid) from Bids GROUP BY AuctionID ORDER BY COUNT(Bidid) DESC" );
			while(rs.next())
			{
				//aantal veilingen limiet
				if (auctions.size() == 5) {
					return auctions;
				}
				Auction a = new Auction();
				a.setAuctionID(rs.getInt("AUCTIONID"));
				a.setUserID(rs.getInt("COUNT(Bidid)"));
				a.setAuctionName(AuctionMethods.getAuctionData(rs.getInt("AUCTIONID"), ServerServiceImpl.getConnection()).getAuctionName());
				auctions.add(a);
			}
			stmt.close();
			con.close();
		}
		catch(SQLException ex) {
			ex.printStackTrace();
		}
		return null;
	}
	public static Bid getHighestBidYear(int year, Connection con) {
		String query = "SELECT MAX(price), bidID FROM bids WHERE extract(year FROM biddate) = ? GROUP BY bidID";
		try {
			PreparedStatement ps = con.prepareStatement(query);
			ps.setInt(1, year);
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				Bid b = new Bid();
				b.setPrice(rs.getInt(1));
				b.setBidID(rs.getInt(2));
				return b;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException noE) { }
		}
		return null;
	}
	public static Bid getHighestBidMonth(int month, int year, Connection con) {
		String query = "SELECT MAX(price), bidID FROM bids WHERE extract(year FROM biddate) = ? AND extract(month FROM biddate) = ? GROUP BY bidID";
		try {
			PreparedStatement ps = con.prepareStatement(query);
			ps.setInt(1, year);
			ps.setInt(2, month);
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				Bid b = new Bid();
				b.setPrice(rs.getInt(1));
				b.setBidID(rs.getInt(2));
				return b;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException noE) { }
		}
		return null;
	}
	public static Bid getHighestBidWeek(int startDay, int endDay, int month, int year, Connection con) {
		String query = "SELECT MAX(price), bidID FROM bids WHERE extract(year FROM biddate) = ? AND extract(month FROM biddate) = ? AND extract(day FROM biddate) >= ? AND extract(day FROM biddate) <= ? GROUP BY bidID";
		try {
			PreparedStatement ps = con.prepareStatement(query);
			ps.setInt(1, year);
			ps.setInt(2, month);
			ps.setInt(3, startDay);
			ps.setInt(4, endDay);
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				Bid b = new Bid();
				b.setPrice(rs.getInt(1));
				b.setBidID(rs.getInt(2));
				return b;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException noE) { }
		}
		return null;
	}
	public static Bid getHighestBidDay(int day, int month, int year, Connection con) {
		String query = "SELECT MAX(price), bidID FROM bids WHERE extract(year FROM biddate) = ? AND extract(month FROM biddate) = ? AND extract(day FROM biddate) = ? GROUP BY bidID";
		try {
			PreparedStatement ps = con.prepareStatement(query);
			ps.setInt(1, year);
			ps.setInt(2, month);
			ps.setInt(3, day);
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				Bid b = new Bid();
				b.setPrice(rs.getInt(1));
				b.setBidID(rs.getInt(2));
				return b;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException noE) { }
		}
		return null;
	}
/*
--hoogste bieding per jaar
SELECT MAX(price), bidID FROM bids WHERE extract(year FROM biddate) = 2011 GROUP BY bidID;

--hoogste bieding per maand
SELECT MAX(price), bidID FROM bids WHERE extract(year FROM biddate) = 2011 AND extract(month FROM biddate) = 10 GROUP BY bidID;

--hoogste bieding per week
SELECT MAX(price), bidID FROM bids WHERE extract(year FROM biddate) = 2011 AND extract(month FROM biddate) = 10 AND extract(day FROM biddate) >= 7 AND extract(day FROM biddate) <= 14 GROUP BY bidID

--hoogste bieding per week
SELECT MAX(price), bidID FROM bids WHERE extract(year FROM biddate) = 2011 AND extract(month FROM biddate) = 10 AND extract(day FROM biddate) = 26 GROUP BY bidID
*/
}
